package co.gov.medellin.ssti.sub.model.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import com.cs.beneficios.business.model.AreaVO;

import co.gov.medellin.ssti.sub.common.ConexionFactory;
import co.gov.medellin.ssti.sub.common.excepcion.SUBException;
import co.gov.medellin.ssti.sub.model.dao.IReporteTotalProgramasDAO;
import co.gov.medellin.ssti.sub.model.vo.ReporteTotalProgramasVO;

public class ReporteTotalProgramasDAOImpl extends BaseDAOImpl implements
		IReporteTotalProgramasDAO {
	private static final String SELECT_SECRETARIAS = "SELECT NOMBRE, CODIGO FROM TCS_BEN_AREA "
			+ "WHERE AREA_CODIGO IS NULL";

	public Collection findReporteTotalProgramas(ReporteTotalProgramasVO criteria)
			throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		ArrayList lTotalProg = new ArrayList();
		try {
			StringBuffer query = new StringBuffer();
			List queryParams = new ArrayList();
			List parametrosAdicionalesParams = new ArrayList();
			List sbQueryVarParams = new ArrayList();

			// para la subconsulta total beneficiarios
			StringBuffer sbQueryVars = new StringBuffer();
			StringBuffer parametrosAdicionales = new StringBuffer("");
			// variables de la consulta
			// codigo de la secretaria
			if ((Object) criteria.getCodeArea() != null) {
				parametrosAdicionales.append("AND area.codigo = ? \n");
				parametrosAdicionalesParams.add(criteria.getCodeArea());
				sbQueryVars.append("AND area_.codigo = ? \n");
				sbQueryVarParams.add(criteria.getCodeArea());
			}
			// codigo del programa
			if ((Object) criteria.getCodePrograma() != null) {
				parametrosAdicionales.append("AND prog.codigo = ? \n");
				parametrosAdicionalesParams.add(criteria.getCodePrograma());
				sbQueryVars.append("AND prog_.codigo = ? \n");
				sbQueryVarParams.add(criteria.getCodePrograma());

			}
			// codigo del subprograma/beneficio
			if (criteria.getCodeSubprograma() != null) {
				parametrosAdicionales.append("AND subprog.codigo = ? \n");
				parametrosAdicionalesParams.add(criteria.getCodeSubprograma());
				sbQueryVars.append("AND subprog_.codigo = ? \n");
				sbQueryVarParams.add(criteria.getCodeSubprograma());
			}
			// fecha inicial del reporte
			if ((Object) criteria.getDesde() != null) {
				if (criteria.getDesde().length() > 0) {
					parametrosAdicionales
							.append("AND bot.fecha_beneficio >= TO_DATE(?,'dd/mm/rrrr') \n");
					parametrosAdicionalesParams.add(criteria.getDesde());
					sbQueryVars
							.append("AND bot_.fecha_beneficio >= TO_DATE(?,'dd/mm/rrrr')\n");
					sbQueryVarParams.add(criteria.getDesde());
				}
			}
			// fecha final del reporte
			if ((Object) criteria.getHasta() != null) {
				if (criteria.getDesde().length() > 0) {
					parametrosAdicionales
							.append("AND bot.fecha_beneficio <= TO_DATE(?,'dd/mm/rrrr')\n");
					parametrosAdicionalesParams.add(criteria.getHasta());
					sbQueryVars
							.append("AND bot_.fecha_beneficio <= TO_DATE(?,'dd/mm/rrrr')\n");
					sbQueryVarParams.add(criteria.getHasta());
				}
			}

			// consulta del reporte:
			query.append("SELECT\n");
			query.append("area.nombre NOMBRE_AREA,\n");
			query.append("subarea.nombre NOMBRE_SUBAREA,\n");
			query.append("prog.nombre NOMBRE_PROGRAMA,\n");
			query.append("subprog.nombre NOMBRE_SUBPROGRAMA,\n");
			query.append("COUNT(DISTINCT(bot.ben_codigo)) BENEFICIARIOS,\n");
			query.append("COUNT(DISTINCT(bot.codigo)) BENEFICIOS,\n");
			query.append("SUM(NVL(bot.valor_beneficio ,0)) VALOR\n");

			// subconsulta total beneficiarios
			// subquery
			query.append(" , (SELECT COUNT(DISTINCT(ben_.codigo)) FROM tcs_ben_area subarea_ JOIN tcs_ben_subprograma subprog_ ON subarea_.codigo = subprog_.area_codigo JOIN tcs_ben_beneficio_otorgado bot_ ON bot_.sbpr_codigo = subprog_.codigo JOIN tcs_ben_beneficiario ben_ ON bot_.ben_codigo = ben_.codigo JOIN tcs_ben_programa prog_ ON subprog_.prg_codigo = prog_.codigo JOIN tcs_ben_area area_ ON area_.codigo = subarea_.area_codigo WHERE 1=1 AND prog_.vigencia = 'A' AND subprog_.vigencia = 'A'\n");
			query.append(sbQueryVars.toString());
			queryParams.addAll(sbQueryVarParams);
			query.append(" ) AS TOTAL_BENEFICIARIOS\n");

			query.append("FROM\n");
			query.append("tcs_ben_area subarea\n");
			query.append("JOIN\n");
			query.append("tcs_ben_subprograma subprog\n");
			query.append("ON subarea.codigo = subprog.area_codigo\n");
			query.append("JOIN\n");
			query.append("tcs_ben_beneficio_otorgado bot\n");
			query.append("ON bot.sbpr_codigo = subprog.codigo\n");
			query.append("JOIN tcs_ben_beneficiario ben\n");
			query.append("ON bot.ben_codigo = ben.codigo\n");
			query.append("JOIN tcs_ben_programa prog\n");
			query.append("ON subprog.prg_codigo = prog.codigo\n");
			query.append("JOIN tcs_ben_area area\n");
			query.append("ON area.codigo = subarea.area_codigo\n");
			// por si no hay parametros adicionalles
			query.append("WHERE 1=1\n");
			// append parametros adicionales
			query.append(parametrosAdicionales.toString());
			queryParams.addAll(parametrosAdicionalesParams);
			query.append("AND prog.vigencia = 'A'\n");
			query.append("AND subprog.vigencia = 'A'\n");
			query.append("GROUP BY area.nombre,subarea.nombre,prog.nombre,subprog.nombre\n");
			query.append("ORDER BY area.nombre,subarea.nombre,prog.nombre,subprog.nombre\n");

			// System.out.println("query: " + query.toString());

			con = ConexionFactory.getInstance().getConnection();
			// PRECOMPILA LA SENTENCIA SQL
			pstmt = con.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, queryParams);
			rst = pstmt.executeQuery();

			ReporteTotalProgramasVO objProgramas = null;

			long subtotalBeneficiosOtorgados = 0;
			long subtotalDiferentesBeneficios = 0;
			long subtotalValor = 0;

			// nombre del programa para comparar con el siguiente registro y
			// saber si hay que agregar fila de subtotales.
			String strProg = null;

			int firstRow = 0;
			long totalBeneficiarios = 0;
			long totalProgramas = 0;
			long totalSubprogramas = 0;
			// cantidad total de beneficios otorgados.
			long totalBeneficios = 0;
			// valor total de los beneficios otorgados.
			long totalValor = 0;

			while (rst.next()) {
				totalValor += rst.getLong("VALOR");
				totalBeneficios += rst.getLong("BENEFICIOS");
				totalSubprogramas++;

				objProgramas = new ReporteTotalProgramasVO();
				if (firstRow == 0) {
					strProg = rst.getString("NOMBRE_PROGRAMA");
					totalBeneficiarios = rst.getLong("TOTAL_BENEFICIARIOS");
					firstRow++;
				}
				// si el nombre del programa de este registro no es igual al
				// anterior se debe agregar subtotales.
				if (!strProg.equals(rst.getString("NOMBRE_PROGRAMA"))) {
					// subtotalBeneficiosOtorgados = rst.getLong("BENEFICIOS");
					strProg = rst.getString("NOMBRE_PROGRAMA");
					// agrega la fila de subtotales del programa anterior.
					objProgramas = new ReporteTotalProgramasVO();
					objProgramas.setNombreArea("SUBTOTAL:");
					objProgramas.setNombreSubprograma(Long
							.toString(subtotalDiferentesBeneficios));
					objProgramas.setCantidadBeneficios(new Long(
							subtotalBeneficiosOtorgados));
					objProgramas.setValorBeneficios(new Long(subtotalValor));
					lTotalProg.add(objProgramas);
					totalProgramas++;

					subtotalValor = rst.getLong("VALOR");
					subtotalBeneficiosOtorgados = rst.getLong("BENEFICIOS");
					subtotalDiferentesBeneficios = 1;

					// agrega la fila del programa actual.
					objProgramas = new ReporteTotalProgramasVO();
					objProgramas.setNombreArea(rst.getString("NOMBRE_AREA"));
					objProgramas.setNombreSubarea(rst
							.getString("NOMBRE_SUBAREA"));
					objProgramas.setNombrePrograma(rst
							.getString("NOMBRE_PROGRAMA"));
					objProgramas.setNombreSubprograma(rst
							.getString("NOMBRE_SUBPROGRAMA"));
					objProgramas.setCantidadBeneficiarios(rst
							.getString("BENEFICIARIOS"));
					objProgramas.setCantidadBeneficios(new Long(rst
							.getLong("BENEFICIOS")));
					objProgramas.setValorBeneficios(new Long(rst
							.getLong("VALOR")));
					// totalBeneficios += rst.getLong("BENEFICIOS");
					lTotalProg.add(objProgramas);

				} else {// mismo programa
					subtotalDiferentesBeneficios++;
					subtotalValor += rst.getLong("VALOR");
					// totalBeneficios += rst.getLong("BENEFICIOS");
					subtotalBeneficiosOtorgados += rst.getLong("BENEFICIOS");
					strProg = rst.getString("NOMBRE_PROGRAMA");

					objProgramas.setNombreArea(rst.getString("NOMBRE_AREA"));
					objProgramas.setNombreSubarea(rst
							.getString("NOMBRE_SUBAREA"));
					objProgramas.setNombrePrograma(rst
							.getString("NOMBRE_PROGRAMA"));
					objProgramas.setNombreSubprograma(rst
							.getString("NOMBRE_SUBPROGRAMA"));
					objProgramas.setCantidadBeneficiarios(rst
							.getString("BENEFICIARIOS"));
					objProgramas.setCantidadBeneficios(new Long(rst
							.getLong("BENEFICIOS")));
					objProgramas.setValorBeneficios(new Long(rst
							.getLong("VALOR")));
					lTotalProg.add(objProgramas);
				}
			}
			if (lTotalProg != null && lTotalProg.size() > 0) {
				// ultima fila de subtotales
				objProgramas = new ReporteTotalProgramasVO();
				objProgramas.setNombreArea("SUBTOTAL:");
				objProgramas.setNombreSubprograma(Long
						.toString(subtotalDiferentesBeneficios));
				objProgramas.setCantidadBeneficios(new Long(
						subtotalBeneficiosOtorgados));
				objProgramas.setValorBeneficios(new Long(subtotalValor));
				lTotalProg.add(objProgramas);
				totalProgramas++;

				// ultimas filas de totales.
				objProgramas = new ReporteTotalProgramasVO();
				objProgramas.setNombreArea("TOTAL:");
				objProgramas.setNombreSubarea("Beneficios Otorgados:");
				objProgramas.setNombrePrograma(formatoNumerico
						.format(totalBeneficios));
				objProgramas.setNombreSubprograma("PROGRAMAS:");
				objProgramas.setCantidadBeneficiarios(formatoNumerico
						.format(totalProgramas));
				lTotalProg.add(objProgramas);

				objProgramas = new ReporteTotalProgramasVO();
				objProgramas.setNombreSubarea("Valor Beneficios Otorgados:");
				objProgramas.setNombrePrograma(formatoNumerico
						.format(totalValor));
				objProgramas.setNombreSubprograma("SUBPROGRAMAS:");
				objProgramas.setCantidadBeneficiarios(formatoNumerico
						.format(totalSubprogramas));
				lTotalProg.add(objProgramas);

				objProgramas = new ReporteTotalProgramasVO();
				objProgramas.setNombreSubarea("Beneficiarios:");
				objProgramas.setNombrePrograma(formatoNumerico
						.format(totalBeneficiarios));
				lTotalProg.add(objProgramas);
			}
		} finally {
			close(con, pstmt, rst);
		}
		return lTotalProg;
	}

	public Collection findSecretarias() throws SQLException {
		Connection con = null;
		ResultSet rst = null;
		PreparedStatement pstmt = null;
		ArrayList lareas = new ArrayList();
		try {
			con = ConexionFactory.getInstance().getConnection();
			pstmt = con.prepareStatement(SELECT_SECRETARIAS);
			rst = pstmt.executeQuery();
			AreaVO objarea = null;
			while (rst.next()) {
				objarea = new AreaVO();
				objarea.setCode(new Long(rst.getString("CODIGO")));
				objarea.setNombre(rst.getString("NOMBRE"));
				lareas.add(objarea);
				objarea = null;
			}

		} catch (Exception e) {
			SUBException.logDebugError("Error findSecretarias: ", e);
		} finally {
			close(con, pstmt, rst);
		}
		return lareas;
	}

	public ReporteTotalProgramasVO findFilters(
			ReporteTotalProgramasVO reporteTotalProgramasVO)
			throws SQLException {
		String strQuery = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		try {
			if (reporteTotalProgramasVO == null) {
				return new ReporteTotalProgramasVO();
			}

			if (reporteTotalProgramasVO.getCodeArea() != null
					|| reporteTotalProgramasVO.getCodePrograma() != null
					|| reporteTotalProgramasVO.getCodeSubprograma() != null) {
				con = ConexionFactory.getInstance().getConnection();
			} else {
				return reporteTotalProgramasVO;
			}

			if (reporteTotalProgramasVO.getCodeArea() != null) {
				strQuery = "SELECT NOMBRE FROM TCS_BEN_AREA WHERE CODIGO = ?";
				pstmt = con.prepareStatement(strQuery);
				pstmt.setLong(1, reporteTotalProgramasVO.getCodeArea()
						.longValue());
				rst = pstmt.executeQuery();
				rst.next();
				reporteTotalProgramasVO.setNombreArea(rst.getString("NOMBRE"));
			}
			if (reporteTotalProgramasVO.getCodePrograma() != null) {
				strQuery = "SELECT NOMBRE FROM TCS_BEN_PROGRAMA WHERE CODIGO = ?";
				pstmt = con.prepareStatement(strQuery);
				pstmt.setLong(1, reporteTotalProgramasVO.getCodePrograma()
						.longValue());
				rst = pstmt.executeQuery();
				rst.next();
				reporteTotalProgramasVO.setNombrePrograma(rst
						.getString("NOMBRE"));
			}
			if (reporteTotalProgramasVO.getCodeSubprograma() != null) {
				strQuery = "SELECT NOMBRE FROM TCS_BEN_SUBPROGRAMA WHERE CODIGO = ?";
				pstmt = con.prepareStatement(strQuery);
				pstmt.setLong(1, reporteTotalProgramasVO.getCodeSubprograma()
						.longValue());
				rst = pstmt.executeQuery();
				rst.next();
				reporteTotalProgramasVO.setNombreSubprograma(rst
						.getString("NOMBRE"));
			}
		} finally {
			close(con, pstmt, rst);
		}
		return reporteTotalProgramasVO;
	}
}
